﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySkiPatrolEntity;

namespace MySkiPatrolSQL
{
    public static class ArticleSQL
    {
        public static string GetArticleList(ArticleRequest data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_article where EffectiveDate < GETDATE() and ExpirationDate > GETDATE() ");
            sql.Append("and pageid in (select pageid from " + data.DataTransaction.PatrolPrefix + "_page where pagename = 'home')  ");
            if (data.Public) sql.Append("and ispublic = 1 ");
            return sql.ToString();
        }

        public static string GetArticle(Article data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("Select * from " + data.DataTransaction.PatrolPrefix + "_article where articleid = '" + data.ID + "'");
            return sql.ToString();
        }

        public static string DeleteArticle(Article data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + data.DataTransaction.PatrolPrefix + "_article where articleid = '" + data.ID + "'");
            return sql.ToString();
        }

        public static string UpdateArticle(Article data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + data.DataTransaction.PatrolPrefix + "_article ");
            sql.Append("set title = '" + data.Title + "', ");
            sql.Append("body = '" + data.Body + "', ");
            sql.Append("effectivedate = '" + data.EffectiveDate + "', ");
            sql.Append("ExpirationDate = '" + data.ExpiryDate + "', ");
            sql.Append("ispublic = '" + data.IsPublic + "' ");
            sql.Append("where articleid = '" + data.ID + "'");
            return sql.ToString();
        }

        public static string AddArticle(Article data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into " + data.DataTransaction.PatrolPrefix + "_article ");
            sql.Append("(articleid, title, body, effectivedate, ExpirationDate, ispublic) values (");
            sql.Append("'" + data.ID + "', ");
            sql.Append("'" + data.Title + "', ");
            sql.Append("'" + data.Body + "', ");
            sql.Append("'" + data.EffectiveDate + "', ");
            sql.Append("'" + data.ExpiryDate + "', ");
            sql.Append("'" + data.IsPublic + "'");
            sql.Append(")"); 
            return sql.ToString();
        }
    }
}
